use cdb::{Database, Result};
use tempfile::TempDir;

/// 创建测试数据库
async fn create_test_database() -> Result<(Database, TempDir)> {
    let temp_dir = tempfile::tempdir().unwrap();
    let db = Database::new().await?;
    Ok((db, temp_dir))
}

#[tokio::test]
async fn test_not_null_constraint() {
    let (db, _temp_dir) = create_test_database().await.unwrap();
    let mut conn = db.connect().await.unwrap();

    // 创建包含NOT NULL约束的表
    let table_name = format!("test_not_null_{}", std::process::id());
    conn.execute(&format!("CREATE TABLE {} (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        email TEXT NOT NULL,
        age INTEGER
    )", table_name)).await.unwrap();

    // 测试正常插入（所有NOT NULL列都有值）
    let result = conn.execute(&format!("INSERT INTO {} VALUES (1, 'Alice', 'alice@example.com', 30)", table_name)).await;
    assert!(result.is_ok(), "正常插入应该成功");

    // 测试违反NOT NULL约束的插入（name为NULL）
    let result = conn.execute(&format!("INSERT INTO {} VALUES (2, NULL, 'bob@example.com', 25)", table_name)).await;
    assert!(result.is_err(), "插入NULL到NOT NULL列应该失败");
    let error_msg = result.unwrap_err().to_string();
    assert!(error_msg.contains("不能为NULL"), "错误消息应该包含NOT NULL约束信息");

    // 测试违反NOT NULL约束的插入（email为NULL）
    let result = conn.execute(&format!("INSERT INTO {} VALUES (3, 'Charlie', NULL, 35)", table_name)).await;
    assert!(result.is_err(), "插入NULL到NOT NULL列应该失败");

    // 测试可为NULL的列（age可以为NULL）
    let result = conn.execute(&format!("INSERT INTO {} VALUES (4, 'David', 'david@example.com', NULL)", table_name)).await;
    assert!(result.is_ok(), "插入NULL到可为NULL列应该成功");

    // 验证数据
    let result = conn.execute(&format!("SELECT * FROM {} ORDER BY id", table_name)).await.unwrap();
    assert_eq!(result.rows().len(), 2, "应该有2行数据（违反约束的插入被拒绝）");

    println!("NOT NULL约束测试通过 ✅");
}

#[tokio::test]
async fn test_primary_key_constraint() {
    let (db, _temp_dir) = create_test_database().await.unwrap();
    let mut conn = db.connect().await.unwrap();

    // 创建包含PRIMARY KEY约束的表
    let table_name = format!("test_primary_key_{}", std::process::id());
    conn.execute(&format!("CREATE TABLE {} (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        email TEXT
    )", table_name)).await.unwrap();

    // 测试正常插入
    let result = conn.execute(&format!("INSERT INTO {} VALUES (1, 'Alice', 'alice@example.com')", table_name)).await;
    assert!(result.is_ok(), "正常插入应该成功");

    // 测试主键重复插入
    let result = conn.execute(&format!("INSERT INTO {} VALUES (1, 'Bob', 'bob@example.com')", table_name)).await;
    assert!(result.is_err(), "插入重复主键应该失败");
    let error_msg = result.unwrap_err().to_string();
    assert!(error_msg.contains("主键约束违反"), "错误消息应该包含主键约束信息");

    // 测试主键为NULL
    let result = conn.execute(&format!("INSERT INTO {} VALUES (NULL, 'Charlie', 'charlie@example.com')", table_name)).await;
    assert!(result.is_err(), "插入NULL主键应该失败");

    // 测试不同主键值的插入
    let result = conn.execute(&format!("INSERT INTO {} VALUES (2, 'David', 'david@example.com')", table_name)).await;
    assert!(result.is_ok(), "插入不同主键值应该成功");

    // 验证数据
    let result = conn.execute(&format!("SELECT * FROM {} ORDER BY id", table_name)).await.unwrap();
    assert_eq!(result.rows().len(), 2, "应该有2行数据");

    println!("PRIMARY KEY约束测试通过 ✅");
}

#[tokio::test]
async fn test_composite_primary_key_constraint() {
    let (db, _temp_dir) = create_test_database().await.unwrap();
    let mut conn = db.connect().await.unwrap();

    // 创建包含复合主键的表
    let table_name = format!("test_composite_pk_{}", std::process::id());
    conn.execute(&format!("CREATE TABLE {} (
        user_id INTEGER,
        course_id INTEGER,
        grade REAL,
        PRIMARY KEY (user_id, course_id)
    )", table_name)).await.unwrap();

    // 测试正常插入
    let result = conn.execute(&format!("INSERT INTO {} VALUES (1, 101, 85.5)", table_name)).await;
    assert!(result.is_ok(), "正常插入应该成功");

    // 测试复合主键重复
    let result = conn.execute(&format!("INSERT INTO {} VALUES (1, 101, 90.0)", table_name)).await;
    assert!(result.is_err(), "插入重复复合主键应该失败");

    // 测试部分相同的复合主键（应该成功）
    let result = conn.execute(&format!("INSERT INTO {} VALUES (1, 102, 88.0)", table_name)).await;
    assert!(result.is_ok(), "插入部分相同的复合主键应该成功");

    let result = conn.execute(&format!("INSERT INTO {} VALUES (2, 101, 92.0)", table_name)).await;
    assert!(result.is_ok(), "插入部分相同的复合主键应该成功");

    // 验证数据
    let result = conn.execute(&format!("SELECT * FROM {} ORDER BY user_id, course_id", table_name)).await.unwrap();
    assert_eq!(result.rows().len(), 3, "应该有3行数据");

    println!("复合PRIMARY KEY约束测试通过 ✅");
}

#[tokio::test]
async fn test_update_constraints() {
    let (db, _temp_dir) = create_test_database().await.unwrap();
    let mut conn = db.connect().await.unwrap();

    // 创建测试表
    let table_name = format!("test_update_constraints_{}", std::process::id());
    conn.execute(&format!("CREATE TABLE {} (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        email TEXT
    )", table_name)).await.unwrap();

    // 插入测试数据
    conn.execute(&format!("INSERT INTO {} VALUES (1, 'Alice', 'alice@example.com')", table_name)).await.unwrap();
    conn.execute(&format!("INSERT INTO {} VALUES (2, 'Bob', 'bob@example.com')", table_name)).await.unwrap();

    // 测试正常更新
    let result = conn.execute(&format!("UPDATE {} SET name = 'Alice Smith' WHERE id = 1", table_name)).await;
    assert!(result.is_ok(), "正常更新应该成功");

    // 测试更新为NULL（违反NOT NULL约束）
    let result = conn.execute(&format!("UPDATE {} SET name = NULL WHERE id = 1", table_name)).await;
    assert!(result.is_err(), "更新为NULL应该失败");

    // 测试更新主键为重复值
    let result = conn.execute(&format!("UPDATE {} SET id = 2 WHERE id = 1", table_name)).await;
    assert!(result.is_err(), "更新主键为重复值应该失败");

    // 测试更新主键为新值（应该成功）
    let result = conn.execute(&format!("UPDATE {} SET id = 3 WHERE id = 1", table_name)).await;
    assert!(result.is_ok(), "更新主键为新值应该成功");

    println!("UPDATE约束测试通过 ✅");
}

#[tokio::test]
async fn test_mixed_constraints() {
    let (db, _temp_dir) = create_test_database().await.unwrap();
    let mut conn = db.connect().await.unwrap();

    // 创建包含多种约束的复杂表
    let table_name = format!("test_mixed_constraints_{}", std::process::id());
    conn.execute(&format!("CREATE TABLE {} (
        id INTEGER PRIMARY KEY,
        username TEXT NOT NULL,
        email TEXT NOT NULL,
        age INTEGER,
        is_active BOOLEAN NOT NULL
    )", table_name)).await.unwrap();

    // 测试正常插入
    let result = conn.execute(&format!("INSERT INTO {} VALUES (1, 'alice', 'alice@example.com', 25, true)", table_name)).await;
    assert!(result.is_ok(), "正常插入应该成功");

    // 测试多个约束违反
    let result = conn.execute(&format!("INSERT INTO {} VALUES (1, NULL, 'bob@example.com', 30, false)", table_name)).await;
    assert!(result.is_err(), "违反多个约束应该失败");

    // 测试单个约束违反
    let result = conn.execute(&format!("INSERT INTO {} VALUES (2, 'bob', NULL, 30, true)", table_name)).await;
    assert!(result.is_err(), "违反NOT NULL约束应该失败");

    // 测试成功插入
    let result = conn.execute(&format!("INSERT INTO {} VALUES (2, 'bob', 'bob@example.com', NULL, false)", table_name)).await;
    assert!(result.is_ok(), "符合所有约束的插入应该成功");

    println!("混合约束测试通过 ✅");
}
